<?php

    // +----------------------------------------------------------------------
    // | 数据库控制器
    // +----------------------------------------------------------------------
    // | Copyright (c) 2015-2024 http://www.yicmf.com, All rights reserved.
    // +----------------------------------------------------------------------
    // | Author: 微尘 <yicmf@qq.com>
    // +----------------------------------------------------------------------

    namespace app\controller\admin;

    use think\Exception;
    use think\facade\Config;
    use think\facade\Db;
    use think\facade\Session;
    use yicmf\builder\Table;

    class Database extends Common
    {
        protected string $system_db_backup_path = '';
        protected function initialize()
        {
            parent::initialize();
            $this->system_db_backup_path = root_path().'data'.DIRECTORY_SEPARATOR.'backup'.DIRECTORY_SEPARATOR;
        }

        /**
         * 数据库备份/还原列表.
         * @return string
         * @author 微尘 <yicmf@qq.com>
         * @datetime: 2020/2/3 15:05
         */
        public function index()
        {


            $builder = new Table();
            $builder->buttonAjax('admin/Database/export', '立即备份')
                ->keyText('name', '表名')
                ->keyText('engine', '引擎')
                ->keyText('rows', '数据量')
                ->keyText('data_length', '数据大小')
                ->keyText('avg_row_length', '平均大小')
                ->keyText('collation', '字符集')
//                ->keyText('comment', '表备注')
                ->keyLeftLeader('numbers')
                ->data(function() {
                    $list = array_map('array_change_key_case', Db::query('SHOW TABLE STATUS'));
                    foreach ($list as &$item) {
                        $item['data_length'] = format_bytes($item['data_length']);
                        $item['avg_row_length'] = format_bytes($item['avg_row_length']);
                        $item['status'] = 1;
                    }
                    return $list;
                }, false)
                ->actionAjax('optimize?tables={$name}', '优化')
                ->actionAjax('repair?tables={$name}', '修复');
            return $builder->fetch();
        }

        public function bakup()
        {
            $builder = new Table();
            $builder->keyHidden('time')
                ->warning('不支持恢复备份，请手动恢复')
                ->keyText('name', '备份名称')
                ->keyText('part', '卷数')
                ->keyText('compress', '压缩')
                ->keyText('size', '数据大小')
                ->keyText('bak_time', '备份时间')
                ->keyLeftLeader(false)
                ->data(function() {
                    //列出备份文件列表
                    if (!is_dir($this->system_db_backup_path)) {
                        mkdir($this->system_db_backup_path, 0755, true);
                    }
                    $flag = \FilesystemIterator::KEY_AS_FILENAME;
                    $glob = new \FilesystemIterator($this->system_db_backup_path, $flag);
                    $list = [];
                    foreach ($glob as $name => $file) {
                        if (preg_match('/^\d{8,8}-\d{6,6}-\d+\.sql(?:\.gz)?$/', $name)) {
                            $file_info = sscanf($name, '%4s%2s%2s-%2s%2s%2s-%d');
                            $date = "{$file_info[0]}-{$file_info[1]}-{$file_info[2]}";
                            $time = "{$file_info[3]}:{$file_info[4]}:{$file_info[5]}";
                            $part = $file_info[6];
                            if (isset($list["{$date} {$time}"])) {
                                $info = $list["{$date} {$time}"];
                                $info['part'] = max($info['part'], $part);
                                $info['size'] = format_bytes($info['size'] + $file->getSize());
                            } else {
                                $info['part'] = $part;
                                $info['size'] = format_bytes($file->getSize());
                            }
                            $extension = strtoupper(pathinfo($file->getFilename(), PATHINFO_EXTENSION));
                            $info['compress'] = ($extension === 'SQL') ? '-' : $extension;
                            $info['bak_time'] = "{$date} {$time}";
                            $info['time'] = strtotime($info['bak_time']);
                            $info['name'] = $name;
                            $info['status'] = 1;
//                            dump($info);
//                            $list["{$date} {$time}"] = $info;
                            $list[] = $info;
                        }
                    }
                    return array_reverse($list);
                }, false)
//                ->actionLink('d?time={$time}', '还原')
                ->actionDelete('del?time={$time}', '删除');
            return $builder->fetch();
        }

        /**
         * 优化表.
         * @param string $tables 表名
         * @throws Exception
         * @author 微尘 <yicmf@qq.com>
         * @datetime: 2020/2/3 15:04
         */
        public function optimize($tables = null)
        {
            if ($tables) {
                $db = Db::connect();
                if (is_array($tables)) {
                    $tables = implode('`,`', $tables);
                    $list = $db->query("OPTIMIZE TABLE `{$tables}`");
                    if ($list) {
                        $this->_returnSuccess( '数据表优化完成！');
                    } else {
                        $this->_returnError( '数据表优化出错请重试！');
                    }
                } else {
                    $list = $db->query("OPTIMIZE TABLE `{$tables}`");
                    if ($list) {
                        $this->_returnSuccess( "数据表'{$tables}'优化完成！");
                    } else {
                        $this->_returnError( "数据表'{$tables}'优化出错请重试！");
                    }
                }
            } else {
                $this->_returnError( '请指定要优化的表！');
            }
        }

        /**
         * 修复表.
         * @param string $tables 表名
         * @throws Exception
         * @author 微尘 <yicmf@qq.com>
         */
        public function repair($tables = null)
        {
            if ($tables) {
                $db = Db::connect();
                if (is_array($tables)) {
                    $tables = implode('`,`', $tables);
                    $list = $db->query("REPAIR TABLE `{$tables}`");
                    if ($list) {
                        $this->_returnSuccess( '数据表修复完成！');
                    } else {
                        $this->_returnError( '数据表修复出错请重试！');
                    }
                } else {
                    $list = $db->query("REPAIR TABLE `{$tables}`");
                    if ($list) {
                        $this->_returnSuccess( "数据表'{$tables}'修复完成！");
                    } else {
                        $this->_returnError( "数据表'{$tables}'修复出错请重试！");
                    }
                }
            } else {
                $this->_returnError( '请指定要修复的表！');
            }
        }

        /**
         * 删除备份文件.
         */
        public function del()
        {
            if ($this->request->param('time')) {
                $name = date('Ymd-His', $this->request->param('time')) . '-*.sql*';
                $path = $this->system_db_backup_path . $name;
                array_map('unlink', glob($path));
                if (count(glob($path))) {
                    $this->_returnError( '备份文件删除失败，请检查权限！');
                } else {
                    $this->_returnSuccess( '备份文件删除成功！');
                }
            } else {
                $this->_returnError( '参数错误！');
            }
        }

        /**
         * 备份数据库
         * @author 微尘 <yicmf@qq.com>
         */
        public function export()
        {
            try {
                if (!$this->request->isPost()) {
                    throw new Exception('参数错误！');
                }
                if (!is_dir($this->system_db_backup_path)) {
                    mkdir($this->system_db_backup_path, 0755, true);
                }
                // 读取备份配置
                $config = [
                    'path' => $this->system_db_backup_path,
                    'part' => 20971520, // 该值用于限制压缩后的分卷最大长度。单位：B；建议设置20M
                    'compress' => 1,
                    'level' => 9,
                ];
                if (!$this->request->has('id', 'post')) {
                    //检查是否有正在执行的任务
                    $lock = "{$config['path']}backup.lock";
                    if (is_file($lock)) {
                        throw new Exception('检测到有一个备份任务正在执行，请稍后再试！');
                    }
                    //创建锁文件
                    file_put_contents($lock, time());
                    //检查备份目录是否可写
                    if (!is_writable($config['path'])) {
                        throw new Exception('备份目录不存在或不可写，请检查后重试！');
                    }
                    //生成备份文件信息
                    $file = [
                        'name' => date('Ymd-His', time()),
                        'part' => 1,
                    ];
                    Session::set('backup_file', $file);
                    $tables = array_map('array_change_key_case', Db::query('SHOW TABLE STATUS'));
                    //缓存要备份的表
                    Session::set('backup_tables', $tables);
                    //创建备份文件
                    $Database = new \yicmf\tools\Database($file, $config);
                    if (false === $Database->create()) {
                        throw new Exception('初始化失败，备份文件创建失败！');
                    }
                    $data['message'] = '初始化成功！';
                    $data['code'] = -1;
                    $data['id'] = 0;
                    $data['start'] = 0;
                } else {
                    $tables = Session::get('backup_tables');
                    $id = $this->request->post('id');
                    $start = $this->request->post('start');
                    //备份指定表
                    $Database = new \yicmf\tools\Database(Session::get('backup_file'), $config);
                    $start = $Database->backup($tables[$id]['name'], $start);
                    if (false === $start) { //出错
                        throw new Exception('备份出错！');
                    }
                    $data['start1'] = $start;
                    if (0 === $start) {
                        //下一表
                        if (isset($tables[++$id])) {
                            $data['message'] = '表' . $tables[$id]['name'] . '备份完成！';
                            $data['code'] = -1;
                            $data['id'] = $id;
                            $data['start'] = 0;
                        } else { //备份完成，清空缓存
                            unlink($config['path'] . 'backup.lock');
                            Session::delete('backup_tables');
                            Session::delete('backup_file');
                            $data['message'] = '备份完成！';
                            $data['code'] = 0;
                        }
                    } else {
                        $rate = floor(100 * ($start[0] / $start[1]));
                        $data['message'] = '表' . $tables[$id]['name'] . '正在备份...(' . $rate . '%)';
                        $data['code'] = -1;
                        $data['id'] = $id;
                        $data['start'] = $start[0];
                    }
                }
                // 返回订单数据
                $data['reload'] = false;
            } catch (\Exception $e) {
                // 回滚事务
                $data['code'] = 1;
                $data['message'] = $e->getMessage();
            }
            return json($data);
        }

        /**
         * 还原数据库.
         */
        public function import()
        {
            try {
                $part = $this->request->post('part', null);
                $start = $this->request->post('start', null);
                if (!Session::has('backup_list')) {
                    $time = $this->request->param('time');
                    //获取备份文件信息
                    $name = date('Ymd-His', $time) . '-*.sql*';
                    $path = $this->system_db_backup_path . $name;
                    $files = glob($path);
                    $list = [];
                    foreach ($files as $name) {
                        $basename = basename($name);
                        $match = sscanf($basename, '%4s%2s%2s-%2s%2s%2s-%d');
                        $gz = preg_match('/^\d{8,8}-\d{6,6}-\d+\.sql.gz$/', $basename);
                        $list[$match[6]] = [$match[6], $name, $gz];
                    }
                    ksort($list);
                    Session::set('backup_list', $list); //缓存备份列表
                } else {
                    $list = Session::get('backup_list'); //缓存备份列表
                }
                //检测文件正确性
                if (is_null($part) && is_null($start)) { //初始化
                    $last = end($list);
                    if (count($list) === $last[0]) {
                        $data['message'] = '初始化完成';
                        $data['code'] = -1;
                        $data['part'] = 1;
                        $data['start'] = 0;
                    } else {
                        throw new Exception('备份文件可能已经损坏，请检查！');
                    }
                } elseif (is_numeric($part) && is_numeric($start)) {
                    $db = new \yicmf\tools\Database($list[$part], [
                        'path' => $this->system_db_backup_path,
                        'compress' => $list[$part][2]]);
                    $start = $db->import($start);
                    if (false === $start) {
                        throw new Exception('还原数据出错！');
                    } elseif (0 === $start) { //下一卷
                        if (isset($list[++$part])) {
                            $data['message'] = "正在还原...#{$part}";
                            $data['code'] = -1;
                            $data['start'] = 0;
                        } else {
                            $data['message'] = '还原完成';
                            $data['code'] = 0;
                        }
                    } else {
                        $data['code'] = -1;
                        $data['start'] = $start[0];
                        if ($start[1]) {
                            $rate = floor(100 * ($start[0] / $start[1]));
                            $data['message'] = "正在还原...#{$part} ({$rate}%)";
                        } else {
                            $data['message'] = "正在还原...#{$part}";
                        }
                    }
                    $data['part'] = $part;
                } else {
                    throw new Exception('参数错误！');
                }
                // 返回订单数据
                $data['reload'] = false;
            } catch (\Exception $e) {
                Session::delete('backup_list');
                $data['code'] = 1;
                $data['message'] = $e->getMessage();
            }
            return json($data);
        }

    }
